package preparedStatement;

import bean.Customer;
import bean.Order;
import org.junit.Test;
import util.JDBCUtils;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/*
* 使用preparedStatement来实现针对不同表的通用的查询操作
* */
public class PreparedStatementQueryTest {

    @Test
    public void testGetInstance() {
        //customer
        String sql = "SELECT id,name,email,birth FROM `customers` WHERE id = ?";
        Customer instance = getInstance(Customer.class, sql, 1);
        System.out.println(instance.toString());
        //order
        String sql2 = "SELECT order_id orderId,order_name orderName,order_date orderDate FROM `order` WHERE order_id = ?";
        Order instance2 = getInstance(Order.class, sql2, 1);
        System.out.println(instance2.toString());
    }
    //针对不同的表的通用的查询操作，返回表中的一条记录
    public <T> T getInstance(Class<T> clazz, String sql, Object... args) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            //获取连接
            connection = JDBCUtils.getConnection();
            //预编译SQL语句
            preparedStatement = connection.prepareStatement(sql);
            //填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            //执行并获取结果集
            resultSet = preparedStatement.executeQuery();
            //获取结果集中列数
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            //处理数据
            if (resultSet.next()) {
                T t = clazz.newInstance();//创建某个类的对象
                for (int i = 0; i < columnCount; i++) {
                    Object value = resultSet.getObject(i + 1);//获取列的值
                    //获取表的别名getColumnLabel()  获取表的列名getColumnName()
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    //给t对象指定的某个属性，赋值为value。(通过反射)
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);//允许访问私有属性
                    field.set(t, value);
                }
                return t;
            }
        } catch (SQLException | IllegalAccessException | NoSuchFieldException | InstantiationException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtils.closeResourceConnection(connection, preparedStatement, resultSet);
        }
        return null;
    }


    @Test
    public void testGetList() {
        //查询customers表中多条记录
        String sql = "SELECT id,name,email,birth FROM `customers` WHERE id < ?";
        List<Customer> customerList = getForList(Customer.class, sql, 12);
        /*for (Customer customer : customerList) {
            System.out.println(customer.toString());
        }*/
        //lambda表达式
        customerList.forEach(System.out::println);

        //查询order表中多条记录
        String sql2 = "SELECT order_id orderId,order_name orderName,order_date orderDate FROM `order` WHERE order_id < ?";
        List<Order> orderList = getForList(Order.class, sql2, 5);
        orderList.forEach(System.out::println);
    }
    //返回表中多条记录
    public <T> List<T> getForList(Class<T> clazz, String sql, Object... args) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            //获取连接
            connection = JDBCUtils.getConnection();
            //预编译SQL语句
            preparedStatement = connection.prepareStatement(sql);
            //填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            //执行并获取结果集
            resultSet = preparedStatement.executeQuery();
            //获取结果集中列数
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            //处理数据
            //创建集合对象
            ArrayList<T> list = new ArrayList<>();
            while (resultSet.next()) {
                T t = clazz.newInstance();//创建某个类的对象
                //处理结果集一行数据中每一个列：给t对象指定的属性赋值
                for (int i = 0; i < columnCount; i++) {
                    Object value = resultSet.getObject(i + 1);//获取列的值
                    //获取表的别名getColumnLabel()  获取表的列名getColumnName()
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    //给t对象指定的某个属性，赋值为value。(通过反射)
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);//允许访问私有属性
                    field.set(t, value);
                }
                list.add(t);
            }
            return list;
        } catch (SQLException | IllegalAccessException | NoSuchFieldException | InstantiationException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtils.closeResourceConnection(connection, preparedStatement, resultSet);
        }
        return null;
    }
}
